
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.TransactionStrategy;
import hk.ces.oxfam.patch.TransactionStrategyType;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.db.sql.SQLUtil;
import hk.ces.oxfam.patch.util.StringUtil;
import java.sql.Connection;
import java.sql.SQLException;

/**
 *
 * @author ReutyCheng
 */
@TransactionStrategy(TransactionStrategyType.ROLLBACK)
public class FindBatchPatch implements TransactionPatch
{
  private final String batchNo;

  public FindBatchPatch(String batchNo)
  {
    this.batchNo = batchNo;
  }

  public void run(Connection connection) throws SQLException
  {
//    System.out.println(sumAllPaymentAmount(connection) + "|" + sumAllAllocationAmount(connection));
    System.out.println((sumAllPaymentAmount(connection) + "|" + (sumAllAllocationAmount(connection) + 575.60)));
    System.out.println(Double.compare(sumAllPaymentAmount(connection), (sumAllAllocationAmount(connection) + 575.60)));
    System.out.println(((int) (sumAllPaymentAmount(connection) * 100)) == ((int) ((sumAllAllocationAmount(connection) + 575.60) * 100)));
  }

  private SQLResult getPayments(Connection connection) throws SQLException
  {
    String sql = "select * from paymentinfo where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));
    return result;
  }

  private SQLResult getAssignment(Connection connection) throws SQLException
  {
    String sql = "select * from otwteamtotalamount where id in (select teamtotalamounts_id from batch_otwteamtotalamount where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));
    return result;
  }

  private SQLResult getAllocation(Connection connection) throws SQLException
  {
    String sql = "select * from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));
    return result;
  }

  private Long[] getAllAllocationIds(Connection connection) throws SQLException
  {
    String sql = "select * from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));
    return result.getColumnValues("ID", new Long[result.getRowCount()]);
  }

  private Double sumAllocationAmount(Connection connection, String[] ids) throws SQLException
  {
    String sql = "select sum(hkamount) from transaction where id in (" + StringUtil.toString("?", ids.length) + ")";
    Object[] params = ids;
    Double sum = SQLUtil.single(connection, new PreparedSQL(sql, params), Double.class);
    return sum;
  }

  private Double sumAllPaymentAmount(Connection connection) throws SQLException
  {
    String sql = "select sum(hkamount) from paymentinfo where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    Double sum = SQLUtil.single(connection, new PreparedSQL(sql, params), Double.class);
    return sum;
  }

  private Double sumAllAllocationAmount(Connection connection) throws SQLException
  {
    String sql = "select sum(hkamount) from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{batchNo};
    Double sum = SQLUtil.single(connection, new PreparedSQL(sql, params), Double.class);
    return sum;
  }
}
